/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package CONTROLLERS;

import BEANS.ComboItem;
import BEANS.Product;
import BEANS.SecondHandProduct;
import UTIL.DatabaseConnector;
import UTIL.InputValidator;
import UTIL.TableManager;
import VIEW.ProductsUI;
import datechooser.beans.DateChooserCombo;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.JComboBox;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import javax.swing.JDialog;
import javax.swing.JLabel;
/**
 *
 * @author AMD
 */
public class SecHandTradeInController {
    private static DatabaseConnector dbConnector = DatabaseConnector.getInstance();;
    private static TableManager secondHandTableManager;
    private static TableManager addSecondHandTableManager;
    private static TableManager tradeSecondHandTableManager;
    private static TableManager tradeNewSecondHandTableManager;
    private static TableManager tradeInHistoryTableManager;
    private static TableManager swapHistoryTableManager;
    private static TableManager secHandTableManager;
    private static ArrayList<SecondHandProduct> secHandItems;
    private static ArrayList<Product> productList;
    private static JComboBox<ComboItem> category;
    private JTextField itemName;
    private JTextField description; 
    private DateChooserCombo dateOfSwap;
    private JDialog swapToNewDialog;
    private static TableManager newProductSwapTableManager;
    private JLabel productNameSwapInput;
    private JLabel productDescriptionSwapInput;
    private JLabel dateSwappedInput;
    private JLabel categoryInput;
    private JLabel clearMark;
    private JLabel productsClearMark;
    private JLabel multipleProductsClearMark;
    private JLabel singleSecondHandClearMark;
    private JLabel multipleSecondHandClearMark;
    private ProductController productController;
    private JTextField inventoryTableSearchInput;
    private JDialog editDialog;
    private JTextField inputName;
    private JTextField inputDescription;
    private JTextField receiptNumberInput;
    private JComboBox<ComboItem> addSecondHandCategoryComboBox;
    private JComboBox<ComboItem> categoryEditComboBox;
    private JComboBox<ComboItem> viewSecondHandCategoryComboBox;
    private JComboBox<ComboItem> tradeCategoryComboBox;
    private JDialog serialNumberInputDialog;
    private JTextField serialNumberInput;
    private JTextField searchInput;
    private DateChooserCombo fromDate;
    private DateChooserCombo toDate;
    private DateChooserCombo dateFrom;
    private DateChooserCombo dateTo;
    private JTextField multipleProductNameInput;
    private JTextField multipleDescriptionInput;
    private JTextField multipleSearchInput;
    private JTextField productsSearchInput;
    private JComboBox<ComboItem> multipleCategoryInput;
    private DateChooserCombo multipleDateOfSwap;
    private TableManager multipleCustomerItemTableManager;
    private static TableManager multipleSecondHandTableManager;
    private static TableManager multipleProductsTableManager;
    //ViewSecondHandList UI
    
    
    public SecHandTradeInController(JTable secondHandTable, JComboBox<ComboItem> viewSecondHandCategoryComboBox, JLabel clearLabel, JTextField searchInput){
        try{
            this.viewSecondHandCategoryComboBox = viewSecondHandCategoryComboBox;
        
        secondHandTableManager = new TableManager(secondHandTable);
        this.clearMark = clearLabel;
        this.searchInput = searchInput;
        
        secondHandTableManager.hideColumn(0);
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    public SecHandTradeInController(JTable historyTable, DateChooserCombo dateFrom, DateChooserCombo dateTo){
        tradeInHistoryTableManager = new TableManager(historyTable);
        this.dateTo = dateTo;
        this.dateFrom = dateFrom;
    }
    
    public SecHandTradeInController(JTable secHandTable, JTextField searchInput, JLabel clearMark,
    JComboBox<ComboItem> categoryEditComboBox, JTextField inputName, JTextField inputDescription, JDialog editDialog){
        this.searchInput = searchInput;
        this.clearMark = clearMark;
        this.categoryEditComboBox = categoryEditComboBox;
        this.inputName = inputName;
        this.inputDescription = inputDescription;
        this.editDialog = editDialog;
        secHandTableManager = new TableManager(secHandTable);
        secHandTableManager.hideColumn(0);
    }
    //Add/Edit SecondHand UI
    public SecHandTradeInController(JTextField productNameInput, JTextField descriptionInput, JComboBox<ComboItem> addSecondHandCategoryInput, JTable secondHandTable){
        itemName = productNameInput;
        description = descriptionInput;
        this.addSecondHandCategoryComboBox = addSecondHandCategoryInput;
        addSecondHandTableManager = new TableManager(secondHandTable);
        
        
        addSecondHandTableManager.hideColumn(0);
    }
    //Trade SecondHand UI
    public SecHandTradeInController(JTextField productNameInput, 
            JTextField descriptionInput, DateChooserCombo dateOfSwapInput, 
            JComboBox<ComboItem> tradeCategoryComboBox, 
            JTable tradeSecondHandTable, JTable tradeInHistory, 
            DateChooserCombo fromDate, DateChooserCombo toDate, 
            JTextField multipleProductNameInput, 
            JTextField multipleDescriptionInput, 
            JComboBox<ComboItem> multipleCategoryInput, 
            DateChooserCombo multipleDateOfSwap, 
            JTable multipleCustomerItemTable, JTable multipleSecondHandTable, 
            JTextField multipleSearchInput, JLabel clearMark, 
            JLabel singleClearMark, JTextField searchInput){
        itemName = productNameInput;
        description = descriptionInput;
        dateOfSwap = dateOfSwapInput;
        this.addSecondHandCategoryComboBox = tradeCategoryComboBox;
        tradeSecondHandTableManager = new TableManager(tradeSecondHandTable);
        tradeInHistoryTableManager = new TableManager(tradeInHistory);
        this.fromDate = fromDate;
        this.toDate = toDate;
        this.multipleProductNameInput = multipleProductNameInput;
        this.multipleDescriptionInput = multipleDescriptionInput;
        this.multipleCategoryInput = multipleCategoryInput;
        this.multipleDateOfSwap = multipleDateOfSwap;
        this.multipleCustomerItemTableManager = new TableManager(multipleCustomerItemTable);
        multipleSecondHandTableManager = new TableManager(multipleSecondHandTable);
        this.multipleSearchInput = multipleSearchInput;
        this.multipleSecondHandClearMark = clearMark;
        this.singleSecondHandClearMark = singleClearMark;
        this.searchInput = searchInput;
        singleSecondHandClearMark.setVisible(false);
        multipleSecondHandClearMark.setVisible(false);
        Calendar day = Calendar.getInstance();
        day.set(day.get(Calendar.YEAR), day.get(Calendar.MONTH), day.get(Calendar.DATE));
        day.add(Calendar.MONTH, -1);
        fromDate.setSelectedDate(day);
        tradeSecondHandTableManager.hideColumn(0);
        tradeInHistoryTableManager.hideColumn(0);
        multipleCustomerItemTableManager.hideColumn(0);
        multipleSecondHandTableManager.hideColumn(0);
    }
    
    //Trade to New SecondHand UI
    public SecHandTradeInController(JTextField productNameInput, JTextField descriptionInput, 
            DateChooserCombo dateOfSwapInput,  JComboBox<ComboItem> multipleCategoryComboBox, 
            JTextField receiptNumberInput, JTable tradeProductsTable, JTable tradeHistory, 
            JTextField searchInput, JLabel clearMark, DateChooserCombo fromDate, 
            DateChooserCombo toDate, JTextField multipleProductNameInput, 
            JTextField multipleDescriptionInput, JComboBox<ComboItem> tradeNewCategoryComboBox,
            DateChooserCombo multipleDateOfSwap, JTable customerNewItemsToBrandNewTable, 
            JTable multipleProductsTable, JTextField productsSearchInput, JLabel productsClearMark){
        itemName = productNameInput;
        description = descriptionInput;
        dateOfSwap = dateOfSwapInput;
        this.receiptNumberInput = receiptNumberInput;
        this.addSecondHandCategoryComboBox = tradeNewCategoryComboBox;
        swapHistoryTableManager = new TableManager(tradeHistory);
        tradeNewSecondHandTableManager = new TableManager(tradeProductsTable);
        this.searchInput = searchInput;
        this.multipleProductsClearMark = clearMark;
        this.fromDate = fromDate;
        this.toDate = toDate;
        this.multipleProductNameInput = multipleProductNameInput;
        this.multipleDescriptionInput = multipleDescriptionInput;
        this.multipleCategoryInput = multipleCategoryComboBox;
        this.multipleDateOfSwap = multipleDateOfSwap;
        this.multipleCustomerItemTableManager = new TableManager(customerNewItemsToBrandNewTable);
        this.multipleProductsTableManager = new TableManager(multipleProductsTable);
        this.productsSearchInput = productsSearchInput;
        this.productsClearMark = productsClearMark;
        Calendar day = Calendar.getInstance();
        multipleProductsClearMark.setVisible(false);
        productsClearMark.setVisible(false);
        day.set(day.get(Calendar.YEAR), day.get(Calendar.MONTH), day.get(Calendar.DATE));
        day.add(Calendar.MONTH, -1);
        fromDate.setSelectedDate(day);
        productController = new ProductController();
        tradeNewSecondHandTableManager.hideColumn(0);
        tradeNewSecondHandTableManager.hideColumn(5);
        tradeNewSecondHandTableManager.hideColumn(6);
        
        multipleProductsTableManager.hideColumn(0);
        multipleCustomerItemTableManager.hideColumn(0);
    }
    
    private void addToSecondHand(boolean autoClear){
        try{   
            
            Object typeComboBoxitem = addSecondHandCategoryComboBox.getSelectedItem();
            String typeID = ((ComboItem)typeComboBoxitem).getValue();

            JTextField[] inputs = {itemName, description};
            String[] values = {typeID, itemName.getText(), description.getText()};

                dbConnector.insert("INSERT INTO secondhand (used_item_type, used_item_name, remarks) "
                        + "VALUES(?,?,?)", values);
            updateSecondHandTable();
            
            dbConnector.closeConnection();
            if(autoClear){
                InputValidator.clearInput(inputs);       
            }
           
        }catch(SQLException sqlE){
                sqlE.printStackTrace();

                JOptionPane.showMessageDialog(null,"Failed to add Product.","Database error",JOptionPane.ERROR_MESSAGE);
        }
    }
    
     public ArrayList<SecondHandProduct> getSecItems(){
        secHandItems = new ArrayList<SecondHandProduct>();
        try{
            String selectString = "SELECT used_item_id, used_item_name, " +
                "used_item_type, type_name, remarks " +
                "FROM secondhand JOIN type ON type.type_id = used_item_type WHERE status='available' ORDER BY 2";
            ResultSet rs = dbConnector.query(selectString);

            while(rs.next()){
                SecondHandProduct shp = new SecondHandProduct(rs.getString(1), rs.getString(2), 
                    rs.getString(3), rs.getString(4), rs.getString(5));
                secHandItems.add(shp);
            }
            dbConnector.closeConnection();
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
            JOptionPane.showMessageDialog(null, "Incorrect Product SQL query", "Database error", JOptionPane.ERROR_MESSAGE);
        }
        return secHandItems;
    }
     
     public static ArrayList<SecondHandProduct> getSecItemList() {
        return secHandItems;
    }
     
     public void updateSecondHandTable(){
         try{
            secondHandTableManager.clearTableContents();
            addSecondHandTableManager.clearTableContents();
            tradeSecondHandTableManager.clearTableContents();
            secHandTableManager.clearTableContents();
            multipleSecondHandTableManager.clearTableContents();
            getSecItems();
            for(SecondHandProduct shp : secHandItems){
                   String[] transferValues = {shp.getProductID(), shp.getName(), 
                       shp.getDescription(), shp.getTypeName()};
                   secondHandTableManager.addRowContent(transferValues);
                   addSecondHandTableManager.addRowContent(transferValues);
                   tradeSecondHandTableManager.addRowContent(transferValues);
                   secHandTableManager.addRowContent(transferValues);
                   multipleSecondHandTableManager.addRowContent(transferValues);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
     }
     
     public void deleteSelectedItem(){
      
        try{
            String itemID = secondHandTableManager.getIDFromTable(secondHandTableManager.getSelectedRow());
            dbConnector.delete("DELETE FROM secondhand WHERE used_item_id = ?", itemID);
            
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
            JOptionPane.showMessageDialog(null, "Failed to delete item from the Secondhand list.", "Database Error", JOptionPane.ERROR_MESSAGE);
        }
    }

    public void editSelectedItem(){
        try{
        Object category = categoryEditComboBox.getSelectedItem();
        String cat = category.toString();
        String name = inputName.getText();
        String desc = inputDescription.getText();
        String id = secondHandTableManager.getIDFromTable(secondHandTableManager.getSelectedRow());
        String[] values = {id, name, desc, id};
        if(InputValidator.checkInput(inputName.getText(), "Product Name cannot be empty.")){
            
            dbConnector.insert("UPDATE secondhand SET used_item_type = ?, used_item_name = ?, remarks = ? WHERE used_item_id = ?", values);
            updateSecondHandTable();
            inputName.setText("");
            inputDescription.setText("");
            editDialog.dispose();
            JOptionPane.showMessageDialog(null, "Product details successfully updated.", "Edit Product", JOptionPane.INFORMATION_MESSAGE);
        }
        }catch(SQLException SQLe){
            SQLe.printStackTrace();
            JOptionPane.showMessageDialog(null, "Incorrect Product SQL query", "Database error", JOptionPane.ERROR_MESSAGE);
        }
    }
    
    public void openEditDialog(){
        try{
        String product = secHandTableManager.getValueAt(secHandTableManager.getSelectedRow(), 1);
        String description = secHandTableManager.getValueAt(secHandTableManager.getSelectedRow(), 2);
        String categ = secHandTableManager.getValueAt(secHandTableManager.getSelectedRow(), 3);
        ResultSet rs = dbConnector.query("SELECT Type_id FROM type WHERE Type_name = '" + categ + "'");
        rs.next();
        String typeID = rs.getString("Type_id");
        for(int i = 0; i < categoryEditComboBox.getItemCount(); i++){
                    if( categoryEditComboBox.getItemAt(i).getValue().equals(typeID) ){
                        categoryEditComboBox.setSelectedItem(categoryEditComboBox.getItemAt(i));
                    }
                }
        inputName.setText(product);
        inputDescription.setText(description);
        editDialog.setVisible(true);
        }catch(SQLException sqle){
            sqle.printStackTrace();
        }
        }
    
    public void editItem(){
        try{        
        Object category = categoryEditComboBox.getSelectedItem();
        String cat = category.toString();               
        String categoryID = ((ComboItem)category).getValue();
        String name = inputName.getText();
        String desc = inputDescription.getText();
        String id = secHandTableManager.getIDFromTable(secHandTableManager.getSelectedRow());
        String[] values = {categoryID, name, desc, id};
        if(InputValidator.checkInput(inputName.getText(), "Product Name cannot be empty.")){
            
            dbConnector.insert("UPDATE secondhand SET used_item_type = ?, used_item_name = ?, remarks = ? WHERE used_item_id = ?", values);
            updateSecondHandTable();
            inputName.setText("");
            inputDescription.setText("");
            editDialog.dispose();
            JOptionPane.showMessageDialog(null, "Product details successfully updated.", "Edit Product", JOptionPane.INFORMATION_MESSAGE);
        }
        }catch(SQLException SQLe){
            SQLe.printStackTrace();
            JOptionPane.showMessageDialog(null, "Incorrect Product SQL query", "Database error", JOptionPane.ERROR_MESSAGE);
        }
    }
    
    public void deleteItem(){
      
        try{
            String itemID = secHandTableManager.getIDFromTable(secHandTableManager.getSelectedRow());
            String product = secHandTableManager.getValueAt(secHandTableManager.getSelectedRow(), 1);
            int result = JOptionPane.showConfirmDialog(null, "Are you sure you want to delete " + product + " from the Users List?","Warning!" ,JOptionPane.YES_NO_OPTION);
            
            if(result == 0){
            dbConnector.delete("DELETE FROM secondhand WHERE used_item_id = ?", itemID);
            updateSecondHandTable();
            JOptionPane.showMessageDialog(null, "Product successfully deleted.", "Delete Product", JOptionPane.INFORMATION_MESSAGE);
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
            JOptionPane.showMessageDialog(null, "Failed to delete item from the Secondhand list.", "Database Error", JOptionPane.ERROR_MESSAGE);
        }
    }
    
    public void updateSecondhandSwapHistory(){
        tradeInHistoryTableManager.clearTableContents();
        DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
        String toDate = InputValidator.dateFormat(dateFrom);
        String fromDate = InputValidator.dateFormat(dateTo);
        try{
            ResultSet rs = dbConnector.query("SELECT swap_id, date_swapped, T2.used_item_name, T1.used_item_name, name  FROM secondhand_swap LEFT JOIN secondhand T1 USING(used_item_id) LEFT JOIN secondhand_swap_details USING(swap_id) LEFT JOIN secondhand T2 ON customer_item_id = T2.used_item_id  LEFT JOIN product USING(product_id) WHERE date_swapped between '" + toDate + "' and '" + fromDate + "' ORDER BY 2 DESC");
            String previousSwapID = "";
            
            while(rs.next()){
                String date = rs.getString("date_swapped");
                Date dateFormat = dfto.parse(date);
                String dateReport = sdf.format(dateFormat);
                String name = rs.getString("t2.used_item_name");
                String secHand = rs.getString("t1.used_item_name");
                String prod = rs.getString("name");
                String swapID = rs.getString("swap_id");
                
                //String remarks = rs.getString("remarks");
                String desc = "";
                String value = "";
                if(rs.getString("t1.used_item_name") == null){
                    desc = "Swapped to: " + prod;
                    value = "Swapped to Brand New";
                }else{
                    desc = "Swapped to: " + secHand;
                    value = "Swapped to Second Hand";
                }
                if(swapID.equals(previousSwapID)){
                    String cellValue = tradeInHistoryTableManager.getValueAt(tradeInHistoryTableManager.getRowCount()-1, 2);
                    String quantity = tradeInHistoryTableManager.getValueAt(tradeInHistoryTableManager.getRowCount()-1, 3);
                    
                    tradeInHistoryTableManager.setValueAt(tradeInHistoryTableManager.getRowCount()-1, 2, cellValue+", " + name);
                    tradeInHistoryTableManager.setValueAt(tradeInHistoryTableManager.getRowCount()-1, 3, Integer.toString((Integer.parseInt(quantity) + 1)));
                }else{
                    String[] reportDetails = {dateReport, "Trade In", name, "1", desc, value};
                    tradeInHistoryTableManager.addRowContent(reportDetails);
                    previousSwapID = swapID;
                }
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    public void updateSecondhandSwapToNewHistory(){
        try{
            ResultSet rs = dbConnector.query("SELECT date_swapped, used_item_name, remarks, name FROM secondhand_swap JOIN secondhand USING(used_item_id) JOIN product USING(product_id) WHERE used_item_id2 IS NULL");
            DateFormat dffrom = new SimpleDateFormat("yyyy-MM-dd");
        
            SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
            swapHistoryTableManager.clearTableContents();
            while(rs.next()){
                String dateSwapped = rs.getString(1).substring(0, 10);
                String usedItemName = rs.getString(2);
                String productName = rs.getString(4);
                String usedItemDescription = rs.getString(3);
                
                Date date = dffrom.parse(dateSwapped);
                String dateOfSwap = sdf.format(date);
                

                swapHistoryTableManager.addRowContent(new String[]{dateOfSwap, usedItemName, usedItemDescription, productName});
                
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(ParseException pe){
            pe.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    public void addSecondHand(){
        String productName = itemName.getText();
        if(productName.equals("")){
                    JOptionPane.showMessageDialog(null,"Product Name cannot be empty.","Input Error",JOptionPane.ERROR_MESSAGE);
        }else{
        int result = JOptionPane.showConfirmDialog(null, "Add " + itemName.getText() + " to the secondhand list?", "Confirm Trade-In", JOptionPane.YES_NO_OPTION);
            if(result == 0){                                                
                    addToSecondHand(true);
                    JOptionPane.showMessageDialog(null,  productName + " has been added to the Secondhand list.", "Trad-In successful", JOptionPane.INFORMATION_MESSAGE);
                }
            }
    }
    
    public void addToMultipleItemsTable(){
        String productName = multipleProductNameInput.getText();
        String productDescription = multipleDescriptionInput.getText();
        if(productName.equals("")){
            JOptionPane.showMessageDialog(null, "Product Name cannot be empty.", "Input Error", JOptionPane.ERROR_MESSAGE);
        }else{
        Object categoryComboBoxitem = multipleCategoryInput.getSelectedItem();
                ComboItem ci = ((ComboItem)categoryComboBoxitem);
                String categoryID = ci.getValue();
        String productCategory = multipleCategoryInput.getSelectedItem().toString();
        
        multipleCustomerItemTableManager.addRowContent(new String[]{categoryID, productName, productDescription, productCategory});
        multipleProductNameInput.setText("");
        multipleDescriptionInput.setText("");
        }
    }
    
    public void clearMultipleItemsTable(){
        multipleCustomerItemTableManager.clearTableContents();
    }
    
    public void removeSelectedCustomerItem(){
        try{
        multipleCustomerItemTableManager.deleteRow();
        }catch(ArrayIndexOutOfBoundsException aioobe){
            JOptionPane.showMessageDialog(null, "Please select a product from the table.", "No product selected", JOptionPane.ERROR_MESSAGE);
        }
    }
    public void swapSecondHandToSecondHand(){
        String salesDateInput = dateOfSwap.getText().split(",")[1].substring(1).concat(","+dateOfSwap.getText().split(",")[2]);

        DateFormat dffrom = new SimpleDateFormat("MMMMM d, yyyy");
        
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        
        try{
            
            String product = itemName.getText();
            if(product.equals("")){
                JOptionPane.showMessageDialog(null, "Please enter the name of the item you wish to swap.", "Input Error", JOptionPane.ERROR_MESSAGE );
            }else{
                Date date = dffrom.parse(salesDateInput);
                String dateOfSwap = sdf.format(date);
                
                String secondHandName = tradeSecondHandTableManager.getValueAt(tradeSecondHandTableManager.getSelectedRow(), 1);
                String swapItemID = tradeSecondHandTableManager.getIDFromTable(tradeSecondHandTableManager.getSelectedRow());
                int result = JOptionPane.showConfirmDialog(null, " " + product + ", will be traded with " + secondHandName + ". Continue?","Confirm Trade-In transaction", JOptionPane.YES_NO_OPTION);
                if(result == 0){
                addToSecondHand(true);
                ResultSet rs = dbConnector.query("SELECT used_item_id FROM secondhand ORDER BY 1 DESC");
                rs.next();
                String secondHandID = rs.getString(1);
                
                    dbConnector.insert("INSERT INTO secondhand_swap(used_item_id, date_swapped) VALUES(?,?) ", new String[]{swapItemID, dateOfSwap});
                    
                    ResultSet swapIDRS = dbConnector.query("SELECT swap_id FROM secondhand_swap ORDER BY 1 DESC");
                    swapIDRS.next();
                    String swapID = swapIDRS.getString(1);
                    dbConnector.insert("INSERT INTO secondhand_swap_details (swap_id, customer_item_id) VALUES(?, ?)", new String[]{swapID, secondHandID});
                    
                    dbConnector.update("UPDATE secondhand SET status = ? WHERE used_item_id = ?", new String[]{"swapped"}, swapItemID);
                    updateSecondhandSwapHistory();
                    updateSecondHandTable();
                    itemName.setText("");
                    description.setText("");
                    JOptionPane.showMessageDialog(null, secondHandName + " has been swapped with " + product + ".", "Swap Successful", JOptionPane.INFORMATION_MESSAGE );            
                }
            }
                
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(ParseException pe){
            pe.printStackTrace();
        }
    }
    
    public void tradeMultipleSecondHand(){
        try{
            if(multipleCustomerItemTableManager.getRowCount() >0){
                String salesDateInput = multipleDateOfSwap.getText().split(",")[1].substring(1).concat(","+dateOfSwap.getText().split(",")[2]);
                DateFormat dffrom = new SimpleDateFormat("MMMMM d, yyyy");
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                String productID = multipleSecondHandTableManager.getIDFromTable(multipleSecondHandTableManager.getSelectedRow());
                String prodName = multipleSecondHandTableManager.getValueAt(multipleSecondHandTableManager.getSelectedRow(),1);
                
                Date date = dffrom.parse(salesDateInput);
                String dateOfSwap = sdf.format(date);
                int result = JOptionPane.showConfirmDialog(null, "Confirm Trade-In?", "Confirm transaction", JOptionPane.OK_CANCEL_OPTION);
                if(result == 0){
                    dbConnector.update("UPDATE secondhand SET status = 'swapped' WHERE used_item_id = ?", new String[]{productID});
                    dbConnector.insert("INSERT INTO secondhand_swap (used_item_id, date_swapped) VALUES(?,?)", new String[]{productID, dateOfSwap});
                    ResultSet swapIDRS = dbConnector.query("SELECT swap_id FROM secondhand_swap ORDER BY 1 DESC");
                    swapIDRS.next();
                    String swapID = swapIDRS.getString(1);
                    int count = 0;
                    for(int i = 0; i< multipleCustomerItemTableManager.getRowCount(); i++){
                        String productName = multipleCustomerItemTableManager.getValueAt(i, 1);
                        String productCategory = multipleCustomerItemTableManager.getValueAt(i, 0);
                        String productDescription = multipleCustomerItemTableManager.getValueAt(i, 2);
                        
                        dbConnector.insert("INSERT INTO secondhand (used_item_name, remarks, used_item_type, status) VALUES(?,?,?,'available')", new String[]{productName, productDescription, productCategory});
                        ResultSet secondHandRS = dbConnector.query("SELECT used_item_id FROM secondhand ORDER BY 1 DESC");
                        secondHandRS.next();
                        String secondHandID = secondHandRS.getString(1);
                        dbConnector.insert("INSERT INTO secondhand_swap_details (swap_id, customer_item_id) VALUES(?, ?)", new String[]{swapID, secondHandID});
                        count++;
                    }
                    multipleCustomerItemTableManager.clearTableContents();
                    updateSecondHandTable();
                    updateSecondhandSwapHistory();
                    JOptionPane.showMessageDialog(null, count + " customer products were swapped with " + prodName + ".", "Trade successful", JOptionPane.INFORMATION_MESSAGE);
                }
            }else{
                JOptionPane.showMessageDialog(null, "Please add the customer's secondhand products to the table.", "No products from customer", JOptionPane.ERROR_MESSAGE);
            }
        }catch(ArrayIndexOutOfBoundsException aioobe){
            JOptionPane.showMessageDialog(null, "Please select an item from the Secondhand product list", "No product selected", JOptionPane.ERROR_MESSAGE);
        }catch(Exception e){
            e.printStackTrace();
            
        }
    }
    public void swapToNewProduct(){
        String salesDateInput = dateOfSwap.getText().split(",")[1].substring(1).concat(","+dateOfSwap.getText().split(",")[2]);

        DateFormat dffrom = new SimpleDateFormat("MMMMM d, yyyy");
        
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String rNo = receiptNumberInput.getText();
        try{
        if(!rNo.equals("")){
            String item = itemName.getText();
            Date date = dffrom.parse(salesDateInput);
            String dateOfSwap = sdf.format(date);
            
            String productName = tradeNewSecondHandTableManager.getValueAt(tradeNewSecondHandTableManager.getSelectedRow(), 1);
            String productID = tradeNewSecondHandTableManager.getIDFromTable(tradeNewSecondHandTableManager.getSelectedRow());
            String warranty = tradeNewSecondHandTableManager.getValueAt(tradeNewSecondHandTableManager.getSelectedRow(), 6);
            
            addToSecondHand(false);
            ResultSet rs = dbConnector.query("SELECT used_item_id FROM secondhand ORDER BY 1 DESC");
            rs.next();
            
            String secondHandID = rs.getString(1);
            
            
            int result = JOptionPane.showConfirmDialog(null, " " + itemName.getText() + ", will be swapped with "+ productName + ". Continue?", "Confirm Trade in transaction", JOptionPane.YES_NO_OPTION);
            if(result ==0){
                dbConnector.insert("INSERT INTO secondhand_swap(product_id, date_swapped) VALUES(?,?)", new String[]{ productID, dateOfSwap});
                ResultSet swapIDRS = dbConnector.query("SELECT swap_id FROM secondhand_swap ORDER BY 1 DESC");
                    swapIDRS.next();
                    String swapID = swapIDRS.getString(1);
                
                dbConnector.insert("INSERT INTO secondhand_swap_details (swap_id, customer_item_id) VALUES(?, ?)", new String[]{swapID, secondHandID});
            
            //dbConnector.update("UPDATE secondhand SET status = ? WHERE used_item_id = ?", new String[]{"swapped"}, productID);
            
            dbConnector.insert("INSERT INTO sales(sales_date, receipt_number) VALUES(?,?)", new String[]{dateOfSwap,rNo});
            ResultSet sales = dbConnector.query("SELECT sales_id FROM sales ORDER BY 1 DESC");
            sales.next();
            String salesID = sales.getString(1);
            if(Integer.parseInt(warranty)>0){
                String serialNumber = serialNumberInput.getText();
                serialNumberInputDialog.dispose();
                serialNumberInput.setText("");
                dbConnector.insert("INSERT INTO sales_details(sales_id, product_id, quantity_sold, sales_type, serial_number, warranty_duration) VALUES(?,?,?,?,?,?)", new String[]{salesID, productID, "1", "swap", serialNumber, warranty});
            }else{
                dbConnector.insert("INSERT INTO sales_details(sales_id, product_id, quantity_sold, sales_type ) VALUES(?,?,?,?)", new String[]{salesID, productID, "1", "swap"});
            }
            updateSecondhandSwapToNewHistory();
            updateSecondHandTable();
            
            dbConnector.update("UPDATE product SET physical_count = physical_count - 1 WHERE product_id = ?", new String[]{productID});
            JOptionPane.showMessageDialog(null, " Products have been swapped!", "Swap Success", JOptionPane.INFORMATION_MESSAGE );
            ProductsUI.updateAdminProductsTable();
            itemName.setText("");
            description.setText("");
            receiptNumberInput.setText("");

            }
        }else{
            JOptionPane.showMessageDialog(null, "Please enter the receipt number.", "Input Error", JOptionPane.ERROR_MESSAGE );
        }
        }catch(SQLException sqlE){
            if(sqlE.getSQLState().startsWith("23")){
                JOptionPane.showMessageDialog(null, "The receipt number you have entered exists.", "Receipt number Error", JOptionPane.ERROR_MESSAGE);
            }else{
            sqlE.printStackTrace();
            }
        }catch(ArrayIndexOutOfBoundsException aioobe){
            JOptionPane.showMessageDialog(null, "No item selected for swap. Please select a product.", "Selection Error", JOptionPane.ERROR_MESSAGE);
        }
        catch(ParseException pe){
            pe.printStackTrace();
        }
    }
    
    public void swapMultipleToNewProduct(){
        try{
            if(multipleCustomerItemTableManager.getRowCount() >0){
                String salesDateInput = multipleDateOfSwap.getText().split(",")[1].substring(1).concat(","+dateOfSwap.getText().split(",")[2]);
                DateFormat dffrom = new SimpleDateFormat("MMMMM d, yyyy");
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                String productID = multipleProductsTableManager.getIDFromTable(multipleProductsTableManager.getSelectedRow());
                String prodName = multipleProductsTableManager.getValueAt(multipleProductsTableManager.getSelectedRow(),1);
                String totalQuantity = multipleProductsTableManager.getValueAt(multipleProductsTableManager.getSelectedRow(),4);
                Date date = dffrom.parse(salesDateInput);
                String dateOfSwap = sdf.format(date);
                if(!totalQuantity.equals("0")){
                    int result = JOptionPane.showConfirmDialog(null, "Confirm Trade-In?", "Confirm Trade-In", JOptionPane.OK_CANCEL_OPTION);
                    if(result == 0){
                        dbConnector.update("UPDATE secondhand SET status = 'swapped' WHERE used_item_id = ?", new String[]{productID});
                        dbConnector.insert("INSERT INTO secondhand_swap (product_id, date_swapped) VALUES(?,?)", new String[]{productID, dateOfSwap});
                        ResultSet swapIDRS = dbConnector.query("SELECT swap_id FROM secondhand_swap ORDER BY 1 DESC");
                        swapIDRS.next();
                        String swapID = swapIDRS.getString(1);
                        int count = 0;
                        for(int i = 0; i< multipleCustomerItemTableManager.getRowCount(); i++){
                            String productName = multipleCustomerItemTableManager.getValueAt(i, 1);
                            String productCategory = multipleCustomerItemTableManager.getValueAt(i, 0);
                            String productDescription = multipleCustomerItemTableManager.getValueAt(i, 3);

                            dbConnector.insert("INSERT INTO secondhand (used_item_name, remarks, used_item_type, status) VALUES(?,?,?,'available')", new String[]{productName, productDescription, productCategory});
                            ResultSet secondHandRS = dbConnector.query("SELECT used_item_id FROM secondhand ORDER BY 1 DESC");
                            secondHandRS.next();
                            String secondHandID = secondHandRS.getString(1);
                            dbConnector.insert("INSERT INTO secondhand_swap_details (swap_id, customer_item_id) VALUES(?, ?)", new String[]{swapID, secondHandID});
                            count++;

                        }
                        dbConnector.update("UPDATE product SET physical_count = physical_count - 1 WHERE product_id = ?", new String[]{productID});
                        multipleCustomerItemTableManager.clearTableContents();
                        updateSecondHandTable();
                        updateSecondhandSwapHistory();
                        ProductsUI.updateAdminProductsTable();
                        JOptionPane.showMessageDialog(null, count + " customer product(s) were swapped with " + prodName + ".", "Trade successful", JOptionPane.INFORMATION_MESSAGE);
                    }
                }else{
                    JOptionPane.showMessageDialog(null, "There is no available stock for " + prodName +".", "Trade In Failed", JOptionPane.ERROR_MESSAGE);
                }
            }else{
                JOptionPane.showMessageDialog(null, "Please add the customer's secondhand products to the table.", "No products from customer", JOptionPane.ERROR_MESSAGE);
            }
        }catch(ArrayIndexOutOfBoundsException aioobe){
            JOptionPane.showMessageDialog(null, "Please select an item from the Secondhand product list", "No product selected", JOptionPane.ERROR_MESSAGE);
        }catch(Exception e){
            e.printStackTrace();
            
        }
    }
    public void checkIfProductHasWarranty(){
        try{
        boolean exists = false;
            ResultSet r = dbConnector.query("SELECT receipt_number FROM sales WHERE receipt_number = ?", new String[]{ receiptNumberInput.getText()} );
                    r.last();
                    if(r.getRow()>0){
                        exists = true;
                    }
        if(receiptNumberInput.getText().equals("")){
            JOptionPane.showMessageDialog(null, "Please enter the receipt number.", "Receipt number empty", JOptionPane.ERROR_MESSAGE);
        }else if(exists){
            JOptionPane.showMessageDialog(null, "The receipt number you have entered exists.", "Receipt number error", JOptionPane.ERROR_MESSAGE);
        }
        else{
            String warranty = newProductSwapTableManager.getValueAt(newProductSwapTableManager.getSelectedRow(), 6);
            if(Integer.parseInt(warranty)>0){
                serialNumberInputDialog.setVisible(true);
            }else{
                swapToNewProduct();
            }
        }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(IndexOutOfBoundsException ioobe){
            JOptionPane.showMessageDialog(null, "Please select an item to swap.", "Item Empty", JOptionPane.ERROR_MESSAGE);
        }
    }

    public void searchProduct(){
        String productName = searchInput.getText();
        
        ArrayList<SecondHandProduct> productList = getSecItemList();
        secondHandTableManager.clearTableContents();
        
        ArrayList<SecondHandProduct> filteredByCategoryList = filterByCategory(productList);
        for(SecondHandProduct product: filteredByCategoryList){
            if(product.getName().toLowerCase().contains(productName.toLowerCase())){
                String[] adminCompleteValues = {product.getProductID(), product.getName(), product.getDescription(), product.getTypeName()};

                secondHandTableManager.addRowContent(adminCompleteValues);
            }
        }
        if(productName.equals("")){
            clearMark.setVisible(false);
        }else{
            clearMark.setVisible(true);
        }
    }
    public void searchProductsTable(){
        String productName = productsSearchInput.getText();
        multipleProductsTableManager.clearTableContents();
        ArrayList<Product> productsList = productController.getProducts();
        for(Product product:productsList){
            if(product.getName().toLowerCase().contains(productName)){
                multipleProductsTableManager.addRowContent(new String[]{product.getProductID(),
                        product.getName(), product.getTypeName(), 
                        product.getSupplierName(), product.getPhysicalCount()});
            }
        }
        if(productName == ""){
            multipleProductsClearMark.setVisible(false);
        }else{
            multipleProductsClearMark.setVisible(true);
        }
    }
    public void searchSingleSecondHandTable(){
        try{String productName = searchInput.getText();
        
        ArrayList<SecondHandProduct> productList = getSecItemList();
        tradeSecondHandTableManager.clearTableContents();

        for(SecondHandProduct product: productList){
            if(product.getName().toLowerCase().contains(productName.toLowerCase())){
                String[] adminCompleteValues = {product.getProductID(), product.getName(), product.getDescription(), product.getTypeName()};

                tradeSecondHandTableManager.addRowContent(adminCompleteValues);
            }
        }
        if(productName.equals("")){
            singleSecondHandClearMark.setVisible(false);
        }else{
            singleSecondHandClearMark.setVisible(true);
        }
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    public void searchProductTable(){
        String productName = searchInput.getText();
        
        ArrayList<Product> productList = productController.getProducts();
       tradeNewSecondHandTableManager.clearTableContents();
        

        for(Product product: productList){
            if(product.getName().toLowerCase().contains(productName.toLowerCase())){
                String[] adminCompleteValues = {product.getProductID(), product.getName(), product.getTypeName(), product.getSupplierName(), product.getPhysicalCount(), product.getReorderQuantityLevel(), product.getWarranty()};

                tradeNewSecondHandTableManager.addRowContent(adminCompleteValues);
            }
        }
        if(productName.equals("")){
            multipleProductsClearMark.setVisible(false);
        }else{
            multipleProductsClearMark.setVisible(true);
        }
    }
    
    public void searchSecondHandProduct(){
        String productName = searchInput.getText();
        
        ArrayList<SecondHandProduct> productList = getSecItemList();
        secHandTableManager.clearTableContents();
        
        for(SecondHandProduct product: productList){
            if(product.getName().toLowerCase().contains(productName.toLowerCase())){
                String[] adminCompleteValues = {product.getProductID(), product.getName(), product.getDescription(), product.getTypeName()};

                secHandTableManager.addRowContent(adminCompleteValues);
            }
        }
        if(productName.equals("")){
            clearMark.setVisible(false);
        }else{
            clearMark.setVisible(true);
        }
        
        if(productName.equals("")){
            clearMark.setVisible(false);
        }else{
            clearMark.setVisible(true);
        }
    }
    
    public void clear(){
        searchInput.setText("");
        clearMark.setVisible(false);
    }
    
    public void searchMultipleSecondHandTable(){
        String productName = multipleSearchInput.getText();
        
        ArrayList<SecondHandProduct> productList = getSecItemList();
        multipleSecondHandTableManager.clearTableContents();
        
        for(SecondHandProduct product: productList){
            if(product.getName().toLowerCase().contains(productName.toLowerCase())){
                String[] adminCompleteValues = {product.getProductID(), product.getName(), product.getDescription(), product.getTypeName()};

                multipleSecondHandTableManager.addRowContent(adminCompleteValues);
            }
        }
        if(productName.equals("")){
            multipleSecondHandClearMark.setVisible(false);
        }else{
            multipleSecondHandClearMark.setVisible(true);
        }
    }
    public ArrayList<SecondHandProduct> filterByCategory(ArrayList<SecondHandProduct> productList){
         ArrayList<SecondHandProduct> filteredProductList = new ArrayList<SecondHandProduct>();
         if(viewSecondHandCategoryComboBox.getSelectedItem().toString().equals("All")){
             return productList;
         }else{
            for(SecondHandProduct product:productList){
                if(viewSecondHandCategoryComboBox.getSelectedItem().toString().equals(product.getTypeName())){
                    filteredProductList.add(product);
                }
            }
            return filteredProductList;
         }
     }
    
    public void clearSearch(){
        searchInput.setText("");
        searchProduct();
        clearMark.setVisible(false);
    }
    
    public void clearSearch2(){
        searchInput.setText("");
        searchProductTable();
        clearMark.setVisible(false);
    }

    public void clearSearch3(){
        searchInput.setText("");
        searchSecondHandProduct();
        clearMark.setVisible(false);
    }
    public void clearSearch4(){
        multipleSearchInput.setText("");
        searchMultipleSecondHandTable();
        clearMark.setVisible(false);
    }
    public void clearSearch5(){
        searchInput.setText("");
        searchSingleSecondHandTable();
        clearMark.setVisible(false);
    }
    public void clearSearch6(){
        searchInput.setText("");
        searchProductsTable();
    }
    public void filterProducts(){

         ArrayList<SecondHandProduct> productList = getSecItemList();
        
         if(viewSecondHandCategoryComboBox.getSelectedItem()!=null && productList != null && viewSecondHandCategoryComboBox.getSelectedItem()!=null){
            secondHandTableManager.clearTableContents();
            ArrayList<SecondHandProduct> filteredByCategoryList = filterByCategory( productList);
            
            if(clearMark.isVisible()){
                searchProduct();
            }else{
                for(SecondHandProduct product:filteredByCategoryList){

                        String[] productValues = {product.getProductID(), product.getName(), product.getDescription(), product.getTypeName()};

                        secondHandTableManager.addRowContent(productValues);
                  
                }
            }
         }        
    }
    
    public void updateTradeInHistoryTable(){
        tradeInHistoryTableManager.clearTableContents();
        DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
        String toDate = InputValidator.dateFormat(dateFrom);
        String fromDate = InputValidator.dateFormat(dateTo);
        try{
            ResultSet rs = dbConnector.query("SELECT swap_id, date_swapped, T2.used_item_name, T1.used_item_name, name  FROM secondhand_swap LEFT JOIN secondhand T1 USING(used_item_id) LEFT JOIN secondhand_swap_details USING(swap_id) LEFT JOIN secondhand T2 ON customer_item_id = T2.used_item_id  LEFT JOIN product USING(product_id) WHERE date_swapped between '" + toDate + "' and '" + fromDate + "'");
            String previousSwapID = "";
            
            while(rs.next()){
                String date = rs.getString("date_swapped");
                Date dateFormat = dfto.parse(date);
                String dateReport = sdf.format(dateFormat);
                String name = rs.getString(3);
                String secHand = rs.getString(4);
                String prod = rs.getString("name");
                String swapID = rs.getString("swap_id");
                
                //String remarks = rs.getString("remarks");
                String desc = "";
                String value = "";
                if(rs.getString(3) == null){
                    desc = "Swapped to: " + prod;
                    value = "Swapped to Brand New";
                }else{
                    desc = "Swapped to: " + secHand;
                    value = "Swapped to Second Hand";
                }
                if(swapID.equals(previousSwapID)){
                    String cellValue = tradeInHistoryTableManager.getValueAt(tradeInHistoryTableManager.getRowCount()-1, 1);
                    String quantity = tradeInHistoryTableManager.getValueAt(tradeInHistoryTableManager.getRowCount()-1, 2);
                    
                    tradeInHistoryTableManager.setValueAt(tradeInHistoryTableManager.getRowCount()-1, 1, cellValue+", " + name);
                    tradeInHistoryTableManager.setValueAt(tradeInHistoryTableManager.getRowCount()-1, 2, Integer.toString((Integer.parseInt(quantity) + 1)));
                }else{
                    String[] reportDetails = {dateReport, name, "1", desc, value};
                    tradeInHistoryTableManager.addRowContent(reportDetails);
                    previousSwapID = swapID;
                }
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
                
    }
}
